﻿CREATE TRIGGER [dbo].[tr_Note_Resolved]
ON [dbo].[t_Note]
FOR UPDATE
AS

IF @@ROWCOUNT = 0 RETURN
IF NOT UPDATE(IsResolved) RETURN
SET NOCOUNT ON

DECLARE @SC int EXEC @SC = sp__Context_SecurityOff 1
DELETE N_L FROM t_Note_Last N_L INNER JOIN inserted i ON N_L.LoanID = i.LoanID --AND N_L.NoteTypeID = i.NoteTypeID
INSERT INTO t_Note_Last(LoanID,NoteID,NoteTypeID)
SELECT N.LoanID,N.ID,N.NoteTypeID
FROM t_Note N
INNER JOIN(
	SELECT N.LoanID,MAX(N.NoteDate)NoteDate,N.NoteTypeID FROM t_Note N
	INNER JOIN inserted i ON i.LoanID = N.LoanID --AND i.NoteTypeID = N.NoteTypeID
	WHERE N.IsResolved = 1 AND N.NoteUser IN (SELECT DISTINCT sAMAccountName FROM v_ADS_Collector)
	GROUP BY N.LoanID,N.NoteTypeID
)NL ON N.LoanID = NL.LoanID AND N.NoteDate = NL.NoteDate --AND N.NoteTypeID = NL.NoteTypeID

EXEC sp__Context @SC
